package chen.web.blog;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.LinkedList;
import java.util.List;

import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.simple.SimpleJdbcTemplate;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.util.Assert;

import chen.web.BusinessException;
import chen.web.DataSourceProvider;
import chen.web.blog.Category.Position;


class CategoryManagerImpl implements CategoryManager {
	
	private static SimpleJdbcTemplate simpleJdbcTemplate = new SimpleJdbcTemplate(DataSourceProvider.getDataSource());
	private static JdbcTemplate jdbcTemplate = new JdbcTemplate(DataSourceProvider.getDataSource());
	
	private static final String SQL_13 = "select * from web_blog_category where cat_parentId=? and cat_frontId=? and cat_id != ?";

	/**
	 * 每个博客最大栏目数，为0则不限制。默认为0
	 */
	private int maxCategoryCountOfPerBlog = 0;
	
	CategoryManagerImpl(){}
	
	CategoryManagerImpl(int maxCategoryCountOfPerBlog){
		Assert.isTrue(maxCategoryCountOfPerBlog > 0);
		this.maxCategoryCountOfPerBlog = maxCategoryCountOfPerBlog;
	}
	
	private static final String SQL_19 = "select count(*) from web_blog_category where blog_id = ?";
	private int getCategoryCount(Blog blog){
		return simpleJdbcTemplate.queryForInt(SQL_19, blog.getId());
	}
	
	private static final String SQL_12 = "insert into web_blog_category(cat_name,cat_description,cat_parentId,cat_frontId, cat_created, blog_id) values(?,?,?,?,?,?)";
	@Override
	public Category add(final Category category) {
		// TODO Auto-generated method stub
		if(maxCategoryCountOfPerBlog!=0 && getCategoryCount(category.getBelong())>=maxCategoryCountOfPerBlog){
			throw new BusinessException(BusinessException.Category_Count_Limit);
		}
		category.setCreated(new Date());
		final Position position = category.getPosition();
		Assert.notNull(position);
		KeyHolder keyHolder = new GeneratedKeyHolder();
		jdbcTemplate.update(new PreparedStatementCreator(){
			@Override
			public PreparedStatement createPreparedStatement(Connection conn)
					throws SQLException {
				// TODO Auto-generated method stub
				PreparedStatement ps = conn.prepareStatement(SQL_12, PreparedStatement.RETURN_GENERATED_KEYS);
				ps.setString(1, category.getName());
				ps.setString(2, category.getDescription());
				ps.setInt(3, position.getParent()==null?0:position.getParent().getId());
				ps.setInt(4, position.getFront()==null?0:position.getFront().getId());
				ps.setTimestamp(5, new Timestamp(category.getCreated().getTime()));
				ps.setInt(6, category.getBelong().getId());
				return ps;
			}
		}, keyHolder);
		int id = keyHolder.getKey().intValue();
		category.setId(id);
		//更新同极栏目front关系
		try{
			Category temp = simpleJdbcTemplate.queryForObject(SQL_13, new CategoryRowMapper(),position.getParent()==null?0:position.getParent().getId(), position.getFront()==null?0:position.getFront().getId(), id);
			Position pos = new Position();
			pos.setParent(position.getParent());
			pos.setFront(category);
			temp.setPosition(pos);
			updatePosition(temp);
		} catch(EmptyResultDataAccessException e){}
		
		return category;
	}

	private static final String SQL_16 = "select count(*) from web_blog_category where blog_id=? and cat_parentId=?";
	private boolean hasChild(Category cat){
		int parentId = cat.getId();
		int blogId = cat.getBelong().getId();
		if(blogId == 0 || parentId == 0) throw new RuntimeException("没有持久化的数据");
		int count = simpleJdbcTemplate.queryForInt(SQL_16, blogId, parentId);
		return count > 0 ? true : false;
	}
	
	private static final String SQL_2 = "delete from web_blog_category where cat_id = ?";
	@Override
	public void delete(Category category) {
		// TODO Auto-generated method stub
		//不能删除有子栏目的栏目
		if(hasChild(category)){
			throw new RuntimeException("不能删除有子栏目的栏目");
		}
		simpleJdbcTemplate.update(SQL_2, category.getId());
		
		//更新同极栏目front关系
		Position position = category.getPosition();
		try{
			Category temp = simpleJdbcTemplate.queryForObject(SQL_24, new CategoryRowMapper(),position.getParent()==null?0:position.getParent().getId(), category.getId());
			temp.setPosition(position);
			updatePosition(temp);
		} catch(EmptyResultDataAccessException e){}
	}

	private static final String SQL_6 = "update web_blog_category set cat_parentId=?,cat_frontId=? where cat_id =?";
	private void updatePosition(Category cat){
		Position pos = cat.getPosition();
		simpleJdbcTemplate.update(SQL_6, pos.getParent()==null?0:pos.getParent().getId(),
							pos.getFront()==null?0:pos.getFront().getId(),
							cat.getId());
	}
	
	private static final String SQL_3 = "update web_blog_category set cat_name=?,cat_description=? where cat_id=?";
	@Override
	public void update(Category category) {
		// TODO Auto-generated method stub
		simpleJdbcTemplate.update(SQL_3, category.getName(), 
					category.getDescription(),
					category.getId()
				);
	}

	private static class CategoryRowMapper implements RowMapper<Category>{
		@Override
		public Category mapRow(ResultSet rs, int i) throws SQLException {
			// TODO Auto-generated method stub
			int id = rs.getInt("cat_id");
			Category cat = new Category(id);
			cat.setName(rs.getString("cat_name"));
			cat.setDescription(rs.getString("cat_description"));
			int parentId = rs.getInt("cat_parentId");
			int frontId = rs.getInt("cat_frontId");
			Category parent = parentId == 0 ? null : new Category(parentId);
			Category front = frontId == 0 ? null : new Category(frontId);
			Position pos = new Position();
			pos.setParent(parent);
			pos.setFront(front);
			cat.setPosition(pos);
			return cat;
		}
		
	}

	private static final String SQL_9 = "select * from web_blog_category where blog_id = ?";
	@Override
	public List<Category> getRootCategories(Blog blog) {
		// TODO Auto-generated method stub
		List<Category> all = simpleJdbcTemplate.query(SQL_9, new CategoryRowMapper(), blog.getId());
		List<Category> rootCategories = new ArrayList<Category>();
		for(Category cat : all){
			cat.setBelong(blog);
			if(cat.getPosition().getParent() == null){
				rootCategories.add(cat);
			}
		}
		//排序
		sort(rootCategories);
		//设置子栏目
		for(Category root : rootCategories){
			setChildren(root, all);
		}
		return rootCategories;
	}
	
	private void setChildren(Category parent, List<Category> all){
		List<Category> children = new LinkedList<Category>();
		for(Category cat : all){
			if(cat.getPosition().getParent() != null && cat.getPosition().getParent().getId() == parent.getId()){
				children.add(cat);
				cat.getPosition().setParent(parent);
			}
		}
		//排序
		sort(children);
		parent.setChildren(children);
		//递归
		for(Category cat : children){
			setChildren(cat, all);
		}
	}

	/**
	 * 排序
	 * @param list
	 */
	private void sort(List<Category> list){
		Category first = null;
		for(int i=0; i<list.size(); i++){
			Category cat = list.get(i);
			if(cat.getPosition().getFront() == null){
				list.remove(i);
				list.add(0, cat);
				first = cat;
			}
		}
		sort(list, first, 1);
	}
	private void sort(List<Category> list, Category front, int i){
		for(int j=i; j<list.size(); j++){
			Category _front = list.get(j).getPosition().getFront();
			if(_front!=null && _front.getId()==front.getId()){
				Category cat = list.remove(j);
				list.add(i, cat);
				sort(list, cat, i+1);
			}
		}
	}
	
	Category find(List<Category> categories, int categoryId) {
		// TODO Auto-generated method stub
		for(Category cat : categories){
			if(cat.getId() == categoryId){
				return cat;
			} else {
				Category result = find(cat.getChildren(), categoryId);
				if(result != null){
					return result;
				} else {
					continue;
				}
			}
		}
		return null;
	}

	private static final String SQL_24 = "select * from web_blog_category where cat_parentId=? and cat_frontId=?";
	@Override
	public void move(Category cat, Position pos) {
		// TODO Auto-generated method stub
		if(!cat.verifyMove(pos)){throw new PositionException("不能移动");}
		Category oldParent = cat.getPosition().getParent();
		Category oldFront = cat.getPosition().getFront();
		Category newParent = pos.getParent();
		Category newFront = pos.getFront();
		
		Category temp = null;
		//更新以前同极栏目front关系
		try{
			temp = simpleJdbcTemplate.queryForObject(SQL_24, new CategoryRowMapper(), oldParent==null?0:oldParent.getId(), cat.getId());	
			Position position = new Position();
			position.setParent(oldParent);
			position.setFront(oldFront);
			temp.setPosition(position);
			updatePosition(temp);
		} catch(EmptyResultDataAccessException e){}
		//更新新的同极栏目front关系
		try{
			temp = simpleJdbcTemplate.queryForObject(SQL_24, new CategoryRowMapper(), newParent==null?0:newParent.getId(), newFront==null?0:newFront.getId());
			Position position = new Position();
			position.setParent(newParent);
			position.setFront(cat);
			temp.setPosition(position);
			updatePosition(temp);
		} catch(EmptyResultDataAccessException e){}
		//更新移动后的关系 
		cat.setPosition(pos);
		updatePosition(cat);
	}

	@Override
	public Category get(Blog belong, int categoryId) {
		// TODO Auto-generated method stub
		List<Category> roots = this.getRootCategories(belong);
		return this.find(roots, categoryId);
	}
	
}
